package com.nf.mapper;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.nf.entity.Car;
import com.nf.entity.Student2;
import com.nf.entity.TestMoney;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class TestDaoImp implements TestDao {

    @Autowired
    private ComboPooledDataSource pool;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void setPool(ComboPooledDataSource pool) {
        System.out.println("创建一个ComboPooledDataSource实例");
        this.pool = pool;
    }

    public TestDaoImp(){
        System.out.println("第一步：TestDaoImp的实例创建了，"+this);
    }

    @Override
    public float getFloat() throws SQLException {
        Connection conn = pool.getConnection();
        PreparedStatement ps = conn.prepareStatement("select * from meiyuan");
        ResultSet rs = ps.executeQuery();
        TestMoney testMoney = new TestMoney();
        while (rs.next()){
            float money = rs.getFloat("money");
            testMoney.setMoney(money);
        }
        System.out.println(testMoney.getMoney());
        ps.close();
        rs.close();
        conn.close();

        return testMoney.getMoney();
    }

    @Override
    public Car getCar(int discount) {
        String SQL = "select * from Car where id = ?";
        try {
            Connection conn = pool.getConnection();
            PreparedStatement pst = conn.prepareStatement(SQL);
            pst.setInt(1,discount);
            ResultSet rs = pst.executeQuery();
            if(rs.next()){
                Car car = new Car();
                String name = rs.getString("name");
                float privce = rs.getFloat("privce");
                car.setName(name);
                car.setPrivce(privce);
                return car;
            }else {
                conn.close();
                pst.close();
                rs.close();
                return null;
            }
        }catch(Exception e){
            System.out.println("getCar连接异常"+e);
            return null;
        }
    }
    @Override
    public List<Student2> getDaoListStudent2() {
        List<Student2> student2List = new ArrayList<Student2>();
        try {
            Connection conn = pool.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from student2");
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Student2 student2 = new Student2();
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String state = rs.getString("state");
                student2.setId(id);
                student2.setName(name);
                student2.setSex(sex);
                student2.setAge(age);
                student2.setState(state);
                student2List.add(student2);
            }
            conn.close();
            rs.close();
            ps.close();
        }catch (Exception e){
            System.out.println(e);
            return null;
        }
        return student2List;
    }

    @Override
    public boolean getDaoDeteleStudent2(int id) {//
        try {
            Connection conn = pool.getConnection();
            PreparedStatement ps = conn.prepareStatement("delete from student2 where id=?");
            ps.setInt(1, id);
            ps.executeUpdate();
            conn.close();
            ps.close();
            return true;
        }catch (Exception e){
            System.out.println(e);
        }
        return false;
    }

    @Override
    public Student2 getDaoJDBCStudent2() {
        try{
            System.out.println(jdbcTemplate);
            Map<String,Object> studentMap = jdbcTemplate.queryForMap("select * from student2 where id=12");
            Student2 student2 = new Student2();
            student2.setId((int)studentMap.get("id"));
            return student2;
        }catch (Exception e){
            System.out.println("异常+"+e);
        }
        return null;
    }
}
